USE msdb;
GO

-- create default alerts for critical events
PRINT CONCAT(CHAR(13) + CHAR(10), 'Installing default alerts...');
GO

DECLARE @Alerts TABLE (name SYSNAME, severity INT);
INSERT INTO @Alerts VALUES
	(N'Sev. 17 Errors, Insufficient Resources', 17),
	(N'Sev. 18 Errors, Nonfatal Internal Error', 18),
	(N'Sev. 19 Errors, Error in Resource', 19),
	(N'Sev. 20 Errors, Fatal Error in Current Process', 20),
	(N'Sev. 21 Errors, Fatal Error in Database Processes', 21),
	(N'Sev. 22 Errors, Fatal Error Table Integrity Suspect', 22),
	(N'Sev. 23 Errors, Fatal Error Database Integrity Suspect', 23),
	(N'Sev. 24 Errors, Hardware Error', 24),
	(N'Sev. 25 Errors, System Error', 25),
	(N'(823) Corruption Errors', 823),
	(N'(824) Corruption Errors', 824),
	(N'(825) Corruption Errors', 825),
	(N'(832) Errors, Constant page has changed', 832),
	(N'(855) Errors, Uncorrectable hardware memory corruption detected', 855),
	(N'(856) Errors, SQL Server has detected hardware memory corruption, but has recovered the page', 856);

DECLARE @alertname SYSNAME,
	@alertseverity INT, @alertmessageid INT;

DECLARE alert_cursor CURSOR FAST_FORWARD FOR
SELECT
	N'Sample: ' + name AS name,
	CASE WHEN severity <= 25 THEN severity  
	ELSE 0 END AS severity,
	CASE WHEN severity <= 25 THEN 0  
	ELSE severity END AS messageid
FROM @Alerts;

OPEN alert_cursor;

WHILE (1=1)
	BEGIN
		FETCH NEXT
			FROM alert_cursor INTO @alertname, @alertseverity, @alertmessageid
		IF @@FETCH_STATUS < 0 BREAK;
		
		EXECUTE msdb.dbo.sp_add_alert
			@name = @alertname,
			@message_id = @alertmessageid,
			@severity = @alertseverity,
			@enabled = 1,
			@delay_between_responses = 10,
			@database_name = NULL,
			@notification_message = NULL,
			@job_name = NULL,
			@event_description_keyword = NULL,
			@include_event_description_in = 1; -- Email
	END

CLOSE alert_cursor;
DEALLOCATE alert_cursor;
GO

EXECUTE msdb.dbo.sp_add_alert 
	@name = N'Sample: Performance Condition, Total Server Memory',
	@message_id = 0,
	@severity = 0,
	@enabled = 1,
	@delay_between_responses = 10,
	@database_name = NULL,
	@notification_message = NULL,
	@performance_condition = N'Memory Manager|Total Server Memory (KB)||>|23068672',
	@job_name = NULL,
	@event_description_keyword = NULL,
	@include_event_description_in = 1;
GO

EXECUTE msdb.dbo.sp_add_alert 
	@name = N'Sample: (9002) Errors, Full tempdb',
	@message_id = 9002,
	@severity = 0,
	@enabled = 1,
	@delay_between_responses = 10,
	@database_name = N'tempdb',
	@notification_message = NULL,
	@job_name = NULL,
	@event_description_keyword = NULL,
	@include_event_description_in = 1;
GO

PRINT CONCAT(CHAR(13) + CHAR(10), 'Installing default alerts completed.');
GO

/*
EXECUTE msdb.dbo.sp_add_notification 
    @alert_name = N'Sample: (9002) Errors, Full tempdb', 
    @operator_name = N'SQL Server DBAs', -- name of profile
    @notification_method = 1; 
GO
*/

/*
-- add notification to all default alerts
DECLARE @alertname SYSNAME; 

DECLARE alert_cursor CURSOR FAST_FORWARD FOR
SELECT
	name AS alert_name
FROM msdb.dbo.sysalerts
WHERE enabled = 1 AND has_notification = 0
AND name LIKE N'Sample:%';

OPEN alert_cursor;

WHILE (1=1)
	BEGIN
		FETCH NEXT
			FROM alert_cursor INTO @alertname
		IF @@FETCH_STATUS < 0 BREAK;
	
		EXECUTE msdb.dbo.sp_add_notification 
			@alert_name = @alertname, 
			@operator_name = N'SQL Server DBAs', -- name of operator
			@notification_method = 1;

		EXECUTE msdb.dbo.sp_add_notification 
			@alert_name = @alertname, 
			@operator_name = N'SQL Server Failsafe Operator', -- name of failsafe operator
			@notification_method = 1; 
	END

CLOSE alert_cursor;
DEALLOCATE alert_cursor;
*/

-- list all alerts
EXECUTE msdb.dbo.sp_help_alert;
GO

/*
-- remove default alerts
PRINT CONCAT(CHAR(13) + CHAR(10), 'Removing default alerts...');
GO

DECLARE @alertname SYSNAME; 

DECLARE alert_cursor CURSOR FAST_FORWARD FOR
SELECT
	name AS alert_name
FROM msdb.dbo.sysalerts
WHERE name LIKE N'Sample:%';

OPEN alert_cursor;

WHILE (1=1)
	BEGIN
		FETCH NEXT
			FROM alert_cursor INTO @alertname
		IF @@FETCH_STATUS < 0 BREAK;
	
		EXECUTE msdb.dbo.sp_delete_alert @name = @alertname;
	END

CLOSE alert_cursor;
DEALLOCATE alert_cursor;

-- list the default alerts
EXECUTE msdb.dbo.sp_help_alert;
GO
*/